﻿using DotNetCommon;
using DotNetCommon.Accessors;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace DBUtil.Builders
{

    public class CaseSegBuilder
    {
        private DBAccess db = null;
        private List<(object when, object value)> whens = [];
        private object elseValue = null;
        private LambdaExpression caseLambdaExpression;
        private string caseColName = null;
        internal CaseSegBuilder(DBAccess db)
        {
            this.db = db;
        }

        internal CaseSegBuilder(DBAccess db, LambdaExpression lambdaExpression)
        {
            this.db = db;
            this.caseLambdaExpression = lambdaExpression;
        }
        internal CaseSegBuilder(DBAccess db, string colName)
        {
            this.db = db;
            this.caseColName = colName;
        }

        #region when
        public WhenSegBuilder WhenSeg(object obj)
        {
            return new WhenSegBuilder(this, obj);
        }
        public WhenSegBuilder WhenSeg<TEntity>(Expression<Func<TEntity, object>> expression) where TEntity : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            return new WhenSegBuilder(this, expression);
        }
        public WhenSegBuilder WhenSeg<TEntity1, TEntity2>(Expression<Func<TEntity1, TEntity2, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            return new WhenSegBuilder(this, expression);
        }

        public WhenSegBuilder WhenSeg<TEntity1, TEntity2, TEntity3>(Expression<Func<TEntity1, TEntity2, TEntity3, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            return new WhenSegBuilder(this, expression);
        }
        public WhenSegBuilder WhenSeg<TEntity1, TEntity2, TEntity3, TEntity4>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
            where TEntity4 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            return new WhenSegBuilder(this, expression);
        }
        public WhenSegBuilder WhenSeg<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
            where TEntity4 : class, new()
            where TEntity5 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            return new WhenSegBuilder(this, expression);
        }
        public WhenSegBuilder WhenSeg<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, TEntity6>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, TEntity6, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
            where TEntity4 : class, new()
            where TEntity5 : class, new()
            where TEntity6 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            return new WhenSegBuilder(this, expression);
        }
        #endregion

        #region else
        public CaseSegBuilder ElseSeg(object obj)
        {
            elseValue = obj;
            return this;
        }
        public CaseSegBuilder ElseSeg<TEntity>(Expression<Func<TEntity, object>> expression) where TEntity : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            elseValue = expression;
            return this;
        }
        public CaseSegBuilder ElseSeg<TEntity1, TEntity2>(Expression<Func<TEntity1, TEntity2, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            elseValue = expression;
            return this;
        }

        public CaseSegBuilder ElseSeg<TEntity1, TEntity2, TEntity3>(Expression<Func<TEntity1, TEntity2, TEntity3, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            elseValue = expression;
            return this;
        }
        public CaseSegBuilder ElseSeg<TEntity1, TEntity2, TEntity3, TEntity4>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
            where TEntity4 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            elseValue = expression;
            return this;
        }
        public CaseSegBuilder ElseSeg<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
            where TEntity4 : class, new()
            where TEntity5 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            elseValue = expression;
            return this;
        }
        public CaseSegBuilder ElseSeg<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, TEntity6>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, TEntity6, object>> expression)
            where TEntity1 : class, new()
            where TEntity2 : class, new()
            where TEntity3 : class, new()
            where TEntity4 : class, new()
            where TEntity5 : class, new()
            where TEntity6 : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            elseValue = expression;
            return this;
        }
        #endregion

        #region endas
        private string convertSql(object obj)
        {
            if (obj is LambdaExpression lambda)
            {
                return BuilderHelper.ParseSql(lambda, db, null, null); ;
            }
            return db.ConvertToSqlSeg(obj).UnWrap();
        }
        private string endAs(string colName = null)
        {
            var sb = new StringBuilder();
            //case
            sb.Append("case ");
            if (caseLambdaExpression != null)
            {
                var col = convertSql(caseLambdaExpression);
                sb.Append(col);
            }
            else if (caseColName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sb.Append(caseColName);
            }

            //when then
            foreach (var when in whens)
            {
                sb.AppendLine().Append("  when ");
                var col = convertSql(when.when);
                sb.Append(col);
                sb.Append(" then ");
                var val = convertSql(when.value);
                sb.Append(val);
            }
            //else
            if (elseValue != null)
            {
                sb.AppendLine().Append("  else ");
                var col = convertSql(elseValue);
                sb.Append(col);
            }
            //endas
            if (colName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sb.Append(" end ").Append(colName);
            }
            else
            {
                sb.Append(" end");
            }
            var sql = sb.ToString();
            sb.Clear();
            return sql;
        }
        public string EndAs(string colName)
        {
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            return endAs(colName);
        }
        public string EndAs<TEntity>(Expression<Func<TEntity, object>> expression) where TEntity : class, new()
        {
            Ensure.NotNull(expression, nameof(expression));
            var colName = convertSql(expression);
            return EndAs(colName);
        }
        public string End()
        {
            return endAs();
        }
        #endregion

        private void AddWhenThen(object obj, object obj2)
        {
            whens.Add((obj, obj2));
        }

        public class WhenSegBuilder
        {
            private readonly CaseSegBuilder caseSegBuilder;
            private object when;

            internal WhenSegBuilder(CaseSegBuilder caseSegBuilder, object obj)
            {
                this.caseSegBuilder = caseSegBuilder;
                this.when = obj;
            }

            internal WhenSegBuilder(CaseSegBuilder caseSegBuilder, LambdaExpression lambdaExpression)
            {
                this.caseSegBuilder = caseSegBuilder;
                this.when = lambdaExpression;
            }

            #region then
            public CaseSegBuilder Then(object obj)
            {
                caseSegBuilder.AddWhenThen(when, obj);
                return caseSegBuilder;
            }

            public CaseSegBuilder Then<TEntity>(Expression<Func<TEntity, object>> expression) where TEntity : class, new()
            {
                Ensure.NotNull(expression, nameof(expression));
                caseSegBuilder.AddWhenThen(when, expression);
                return caseSegBuilder;
            }
            public CaseSegBuilder CaseSeg<TEntity1, TEntity2>(Expression<Func<TEntity1, TEntity2, object>> expression)
                where TEntity1 : class, new()
                where TEntity2 : class, new()
            {
                Ensure.NotNull(expression, nameof(expression));
                caseSegBuilder.AddWhenThen(when, expression);
                return caseSegBuilder;
            }

            public CaseSegBuilder CaseSeg<TEntity1, TEntity2, TEntity3>(Expression<Func<TEntity1, TEntity2, TEntity3, object>> expression)
                where TEntity1 : class, new()
                where TEntity2 : class, new()
                where TEntity3 : class, new()
            {
                Ensure.NotNull(expression, nameof(expression));
                caseSegBuilder.AddWhenThen(when, expression);
                return caseSegBuilder;
            }
            public CaseSegBuilder CaseSeg<TEntity1, TEntity2, TEntity3, TEntity4>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, object>> expression)
                where TEntity1 : class, new()
                where TEntity2 : class, new()
                where TEntity3 : class, new()
                where TEntity4 : class, new()
            {
                Ensure.NotNull(expression, nameof(expression));
                caseSegBuilder.AddWhenThen(when, expression);
                return caseSegBuilder;
            }
            public CaseSegBuilder CaseSeg<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, object>> expression)
                where TEntity1 : class, new()
                where TEntity2 : class, new()
                where TEntity3 : class, new()
                where TEntity4 : class, new()
                where TEntity5 : class, new()
            {
                Ensure.NotNull(expression, nameof(expression));
                caseSegBuilder.AddWhenThen(when, expression);
                return caseSegBuilder;
            }
            public CaseSegBuilder CaseSeg<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, TEntity6>(Expression<Func<TEntity1, TEntity2, TEntity3, TEntity4, TEntity5, TEntity6, object>> expression)
                where TEntity1 : class, new()
                where TEntity2 : class, new()
                where TEntity3 : class, new()
                where TEntity4 : class, new()
                where TEntity5 : class, new()
                where TEntity6 : class, new()
            {
                Ensure.NotNull(expression, nameof(expression));
                caseSegBuilder.AddWhenThen(when, expression);
                return caseSegBuilder;
            }
            #endregion
        }
    }

    public class CaseListSegBuilder<T> where T : class, new()
    {
        private DBAccess db = null;
        private readonly IEnumerable<T> list;
        private readonly LambdaExpression caseExp;
        private readonly LambdaExpression thenExp;

        internal CaseListSegBuilder(DBAccess db, IEnumerable<T> list, LambdaExpression caseExp, LambdaExpression thenExp)
        {
            this.db = db;
            this.list = list;
            this.caseExp = caseExp;
            this.thenExp = thenExp;
        }

        private const string errMsg = "db.CaseListSeg()使用错误, 正确示例: db.CaseListSeg(list,i=>i.Id,i=>i.Name) 这将生成如: case id when 1 then 'name1' when 2 then 'name2' end\"";
        public override string ToString()
        {
            var caseProps = ExpressionHelper.GetInitOrReturnPropNames(caseExp);
            if (caseProps.IsNullOrEmpty() || caseProps.Count > 1) throw new Exception(errMsg);
            var thenProps = ExpressionHelper.GetInitOrReturnPropNames(thenExp);
            if (thenProps.IsNullOrEmpty() || thenProps.Count > 1) throw new Exception(errMsg);
            var sb = new StringBuilder();
            //case
            var caseProp = caseProps.First();
            sb.Append($"case {db.GetEntityInfoInternal<T>().EntityPropertyInfos.First(i => i.PropNamePure == caseProps.First()).SelectValueFunc(null)}\r\n");
            var thenProp = thenProps.First();
            var accessor = Accessor.Build<T>();
            foreach (var item in list)
            {
                sb.Append($"  when {db.ConvertToSqlSeg(accessor[item, caseProp]).Data} then {db.ConvertToSqlSeg(accessor[item, thenProp]).Data}\r\n");
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append($" end");
            var sql = sb.ToString();
            sb.Clear();
            return sql;
        }
    }
}
